-- @owner: danny20001210
-- @date: 2025-5-15
-- @testpoint:验证xmltype模块结合复杂表达式使用正确，success，部分用例合理报错
@conn PrimaryDbAmode;
drop table if exists table_1266887 cascade;
drop table if exists table_1266887_1 cascade;


create table table_1266887( ID int, name clob, data xmltype );
Insert INTO table_1266887 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;

Insert INTO table_1266887 VALUES (2,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾2</subfield> <subfield code="f">奥运会2</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机2</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机2</subfield> <subfield code="a">笔记本2</subfield> </datafield> </record> </collection>')) ;


create table table_1266887_1( ID int, name clob, data xmltype );
Insert INTO table_1266887_1 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;

Insert INTO table_1266887_1 VALUES (2,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾2</subfield> <subfield code="f">奥运会2</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机2</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机2</subfield> <subfield code="a">笔记本2</subfield> </datafield> </record> </collection>')) ;

Insert INTO table_1266887_1 VALUES (3,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾3</subfield> <subfield code="f">奥运会3</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机3</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机3</subfield> <subfield code="a">笔记本3</subfield> </datafield> </record> </collection>')) ;


select '1'::xmltype='1'::xmltype;
select '1'::xml='1'::xml;
select '1'::xmltype='1'::xmltype;
select '1'::xml='1'::xmltype;

select '1'::xmltype!='1'::xmltype;
select '1'::xml!='1'::xml;
select '1'::xmltype!='1'::xmltype;
select '1'::xml!='1'::xmltype;



--in
SELECT * FROM table_1266887 WHERE id >= ( SELECT AVG(id) FROM table_1266887 WHERE EXISTS ( SELECT 1 FROM UNNEST( xpath( '/collection/record/datafield/subfield/text()', data )::TEXT[] ) AS subfield_value WHERE subfield_value IN ( '抗震救灾', '奥运会', '经济学', '计算机', '10001', '2005-07-09', '计算机', '笔记本' ) ) );

--SELECT * FROM xmltest WHERE (SELECT xpath('/root/value/text()', data)::TEXT) IN ('one', 'two');
select * from table_1266887 x where extractvalue(x.data,'/collection/record/leader') in ('-----nam0-22-----^^^450-') ;

SELECT * FROM table_1266887 WHERE (SELECT xpath('/collection/record/datafield/subfield/text()', data)::TEXT) IN ('{抗震救灾,奥运会,经济学,计算机,10001,2005-07-09,计算机,笔记本}');

SELECT * FROM table_1266887 WHERE (SELECT xpath('/collection/record/datafield/subfield/text()', data)::TEXT) = '{抗震救灾,奥运会,经济学,计算机,10001,2005-07-09,计算机,笔记本}';
--bug
SELECT * FROM table_1266887 where data = xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>');

SELECT * FROM table_1266887 where data in( xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>'));

SELECT * FROM table_1266887 WHERE id >= (SELECT AVG(id) FROM table_1266887 WHERE extract(table_1266887.data,'/collection/record/datafield/subfield')::text IN ('{"<subfield code="a">抗震救灾</subfield>","<subfield code="f">奥运会</subfield>","<subfield code="a">经济学</subfield>","<subfield
code="b">计算机</subfield>","<subfield code="c">10001</subfield>","<subfield code="d">2005-07-09</subfield>","<subfield code="a"
>计算机</subfield>","<subfield code="a">笔记本</subfield>"}') );

--not in
SELECT * FROM table_1266887 WHERE (SELECT xpath('/collection/record/datafield/subfield/text()', data)::TEXT) not IN ('{抗震救灾2,奥运会,经济学,计算机,10001,2005-07-09,计算机,笔记本}');
--bug
SELECT * FROM table_1266887 where data not in (xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>'));

--exists

SELECT * FROM table_1266887 t WHERE EXISTS (SELECT * FROM table_1266887 WHERE data = xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>') AND ID = 1);

SELECT * FROM table_1266887 t WHERE EXISTS (SELECT * FROM table_1266887 WHERE data = xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机2</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>') AND ID = 1);

--not exists

SELECT * FROM table_1266887 t WHERE not EXISTS (SELECT * FROM table_1266887 WHERE data = xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>') AND ID = 1);

SELECT * FROM table_1266887 t WHERE not EXISTS (SELECT * FROM table_1266887 WHERE data = xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机2</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>') AND ID = 1);

--any
SELECT * FROM table_1266887 WHERE data = ANY (SELECT data FROM table_1266887 where data not IN (xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>'))  );
SELECT * FROM table_1266887 WHERE data <> ANY (SELECT data FROM table_1266887 where data not IN (xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>'))  );
SELECT * FROM table_1266887  WHERE extractvalue(data,'/collection/record/leader') = ANY (SELECT extractvalue(data,'/collection/record/leader') FROM table_1266887 where extractvalue(data,'/collection/record/leader') IN ('-----nam0-22-----^^^450-','1')  );

--some
SELECT * FROM table_1266887 WHERE data = some (SELECT data FROM table_1266887 where data not IN (xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>'))  );
SELECT * FROM table_1266887 WHERE data <> some (SELECT data FROM table_1266887 where data not IN (xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>'))  );
SELECT * FROM table_1266887  WHERE extractvalue(data,'/collection/record/leader') = some (SELECT extractvalue(data,'/collection/record/leader') FROM table_1266887 where extractvalue(data,'/collection/record/leader') IN ('-----nam0-22-----^^^450-','1')  );
--all
SELECT * FROM table_1266887 WHERE data = all (SELECT data FROM table_1266887 where data not IN (xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>'))  );
SELECT * FROM table_1266887 WHERE data <> all (SELECT data FROM table_1266887 where data not IN (xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>'))  );
SELECT * FROM table_1266887  WHERE extractvalue(data,'/collection/record/leader') = all (SELECT extractvalue(data,'/collection/record/leader') FROM table_1266887 where extractvalue(data,'/collection/record/leader') IN ('-----nam0-22-----^^^450-','1')  );

--case when
SELECT CASE extractvalue(data,'/collection/record/leader') WHEN '-----nam0-22-----^^^450-' THEN 'this is 1' ELSE '其他' END FROM table_1266887;

SELECT CASE data WHEN '-----nam0-22-----^^^450-' THEN xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')  ELSE '其他' END FROM table_1266887;

--with tmp as
WITH tmp AS (SELECT * FROM table_1266887 where extractvalue(data,'/collection/record/leader') IN ('-----nam0-22-----^^^450-' ,'1890-12-12 20:20:20:999'))
SELECT * FROM table_1266887
WHERE extractvalue(data,'/collection/record/leader') IN
(SELECT extractvalue(data,'/collection/record/leader') FROM tmp UNION SELECT extractvalue(data,'/collection/record/leader') FROM table_1266887 where id =1);

WITH tmp AS (SELECT * FROM table_1266887 where extractvalue(data,'/collection/record/leader') IN ('1888-12-12 20:20:20:999','1890-12-12 20:20:20:999'))
SELECT * FROM table_1266887
WHERE extractvalue(data,'/collection/record/leader') IN
(SELECT extractvalue(data,'/collection/record/leader') FROM tmp UNION SELECT extractvalue(data,'/collection/record/leader') FROM table_1266887 where id =1);


--with recursive tmp as
WITH recursive tmp AS (SELECT id ,extractvalue(data,'/collection/record/leader') FROM table_1266887 where extractvalue(data,'/collection/record/leader') IN ('-----nam0-22-----^^^450-','1890-12-12 20:20:20:999'))
SELECT * FROM table_1266887
WHERE extractvalue(data,'/collection/record/leader') IN
(SELECT extractvalue(data,'/collection/record/leader') FROM tmp WHERE id IN (SELECT MAX(id) FROM tmp));

WITH recursive tmp AS (SELECT id ,extractvalue(data,'/collection/record/leader') FROM table_1266887 where extractvalue(data,'/collection/record/leader') IN ('-----nam0-22-----^^^450-','1890-12-12 20:20:20:999'))
SELECT * FROM table_1266887
WHERE extractvalue(data,'/collection/record/leader') IN
(SELECT extractvalue(data,'/collection/record/leader') FROM tmp WHERE id IN (SELECT MAX(id) FROM tmp));


--相关子查询
SELECT data FROM table_1266887 WHERE id > (SELECT AVG(id) FROM table_1266887 where extractvalue(data,'/collection/record/leader') = '-----nam0-22-----^^^450-');
SELECT data FROM table_1266887 WHERE id > (SELECT AVG(id) FROM table_1266887 where existsnode(data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') = 1);
SELECT data FROM table_1266887 WHERE id > (SELECT AVG(id) FROM table_1266887 where XMLSequence(extract(data,'/collection/record/datafield/subfield')) is not null);
SELECT data FROM table_1266887 WHERE id > (SELECT AVG(id) FROM table_1266887 where extract(data,'/collection/record/datafield/subfield') is not null);
SELECT data FROM table_1266887 WHERE id > (SELECT AVG(id) FROM table_1266887 where getStringVal(extract(data,'/collection/record/datafield/subfield')) is not null);

--非相关子查询
SELECT * FROM table_1266887 WHERE id > (SELECT id! FROM table_1266887 where extractvalue(data,'/collection/record/leader') = '-----nam0-22-----^^^450-');
SELECT * FROM table_1266887 WHERE id > (SELECT id! FROM table_1266887 where existsnode(data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') = 1);
SELECT * FROM table_1266887 WHERE id > (SELECT id! FROM table_1266887 where XMLSequence(extract(data,'/collection/record/datafield/subfield')) is not null);
SELECT * FROM table_1266887 WHERE id > (SELECT id! FROM table_1266887 where extract(data,'/collection/record/datafield/subfield') is not null);
SELECT * FROM table_1266887 WHERE id > (SELECT id! FROM table_1266887 where getStringVal(extract(data,'/collection/record/datafield/subfield')) is not null);



select count(extractvalue(data,'/collection/record/leader')) from table_1266887 t ;
select count(extractvalue(data,'/collection/record/leader')) from table_1266887 t group by data;
select table_1266887.id, table_1266887.data,table_1266887_1.id, table_1266887_1.data
from table_1266887
join
table_1266887_1 on extractvalue(table_1266887.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]') = extractvalue(table_1266887_1.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]') ;
select table_1266887.id, table_1266887.data,table_1266887_1.id, table_1266887_1.data
from table_1266887
inner join
table_1266887_1 on extractvalue(table_1266887.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]') = extractvalue(table_1266887_1.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]') ;
select table_1266887.id, table_1266887.data,table_1266887_1.id, table_1266887_1.data
from table_1266887
left join
table_1266887_1 on extractvalue(table_1266887.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]') = extractvalue(table_1266887_1.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]') ;
select table_1266887.id, table_1266887.data,table_1266887_1.id, table_1266887_1.data
from table_1266887
right join
table_1266887_1 on extractvalue(table_1266887.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]') = extractvalue(table_1266887_1.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]') ;
select table_1266887.id, table_1266887.data,table_1266887_1.id, table_1266887_1.data
from table_1266887
full join
table_1266887_1 on extractvalue(table_1266887.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]') = extractvalue(table_1266887_1.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]') ;

--表达式索引
create index index_1266887_1 on table_1266887 using btree(existsnode(data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]'));
create index index_1266887_2 on table_1266887 using btree(extractvalue(data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]'));
create index index_1266887_3 on table_1266887 using btree(extract(data,'/collection/record/datafield/subfield'));
create index index_1266887_11 on table_1266887 using btree(existsnode(data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]'));
create index index_1266887_22 on table_1266887 using btree(extractvalue(data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]'));
create index index_1266887_33 on table_1266887 using btree(extract(data,'/collection/record/datafield/subfield'));


--dml操作
update table_1266887 set data=appendchildxml(data,'/collection/record/datafield/subfield',XMLType('<label>显著增强的123</label>')) where id =1;
select * from table_1266887;
delete from table_1266887 where extractvalue(table_1266887.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]') = '抗震救灾显著增强的123';
select * from table_1266887;

merge into table_1266887 t1
using table_1266887_1 t2
on t1.id=t2.id
WHEN MATCHED THEN
UPDATE SET name = extractvalue(t2.data,'/collection/record/datafield[@tag="200"]/subfield[@code="a"]')
WHEN NOT MATCHED THEN
insert (t1.id,t1.data) values (t2.id,t2.data);

merge into table_1266887 t1
using table_1266887_1 t2
on t1.id=t2.id
WHEN MATCHED THEN
UPDATE SET data=appendchildxml(data,'/collection/record/datafield/subfield',XMLType('<label>显著增强的1234</label>'))
WHEN NOT MATCHED THEN
insert (t1.id,t1.data) values (t2.id,t2.data);

select data from table_1266887 where data is null;
select data from table_1266887 where data is not null;
select distinct data from table_1266887 where data || '??' is not null;
select data from table_1266887 group by data ;
select data from table_1266887 group by data having count(*) >1 ;


drop table if exists table_1266887 cascade;
drop table if exists table_1266887_1 cascade;


